package common.db;

import common.db.handlers.ResultSetHandler;
import common.db.handlers.OneRowHandler;
import common.db.repository.SqlQueryParam;
import common.db.io.MyLog;

import javax.sql.DataSource;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import java.io.StringReader;
import java.io.BufferedReader;
import java.io.IOException;

import sun.org.mozilla.javascript.internal.Token;

/**
 * Created by IntelliJ IDEA.
 * User: Paskal
 * Date: Apr 6, 2009
 * Time: 6:47:03 PM
 * To change this template use File | Settings | File Templates.
 */
public class QueryRunner
{
	protected boolean autoCommit = false;
	protected DataSource dataSource;  // ovo ce zameniti DBPool
	private ConnectionParametar connectionParametar;

	private MyLog log = new MyLog(this.getClass());

	public ConnectionParametar getConnectionParametar()
	{
		return connectionParametar;
	}/*
	* QueryRunner with predefined DBParam
	* **/

	public QueryRunner()
	{
		connectionParametar = new ConnectionParametar();
	}

	/*
		* QueryRunner with our DBParam
		* **/
	public QueryRunner(ConnectionParametar dbParam)
	{
		this.connectionParametar = dbParam;
	}

	public QueryRunner(boolean autoCommit)
	{
		this.autoCommit = autoCommit;
	}


	/**
	 * Executes SQL-query. Returns ResultSet.
	 * This method should be used with care because caller must close ResultSet with releseResultSet method .
	 * ResultSet is created with ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.CONCUR_READ_ONLY flags.
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public ResultSet queryRs(String sql, Object... params)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Connection connection = null;
		long timeStamp = 0;
		try
		{
			connection = checkConnection();
			stmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			this.fillStatement(stmt, params);
			timeStamp = timeStampBegin();
			rs = stmt.executeQuery();
			timeStamp = timeStampEnd(timeStamp);

		}
		catch (SQLException e)
		{
			rethrow(e, sql, params);
		}
		return rs;
	}

	/**
	 * Closes ResultSet given from queryRs method
	 * This method must be used for closing result sets because of proper connecton managment
	 *
	 * @param rs active result det from queryRs method
	 */
	public void releseResultSet(ResultSet rs)
	{
		try
		{
			Statement stmt = rs.getStatement();
			Connection conn = stmt.getConnection();
			close(rs);
			try
			{
				close(stmt);
			}
			finally
			{
				releaseConnection(conn);
			}
		}
		catch (SQLException e)
		{
			throw new RuntimeException(e);
		}
	}

	/**
	 * Simplifier Sql-Query
	 *
	 * @param sqlParam
	 * @param rsh
	 * @return
	 */
	public <E> E query(SqlQueryParam sqlParam, ResultSetHandler<E> rsh)
	{
		return query(sqlParam.getSql(), rsh, sqlParam.getParams());
	}

	/**
	 * Correcting syntax, because when we have null field it must be use some_field is null,
	 * not some_field = null
	 */
	private String correctSyntaxForNullParams(String sql, Object... params)
	{
		if (params == null || params.length == 0)
			return sql;
		boolean mustCorrect = false;
		for (Object o : params)
		{
			if (o == null)
				mustCorrect = true;
		}
		if (!mustCorrect)
			return sql;
		if (params != null && params.length > 0)
		{
			int index = 0;
			String[] divide = sql.split("\\?");
			StringBuilder sb = new StringBuilder();
			for (Object o : params)
			{
				if (o == null)
				{
					//divide[index] = divide[index].replaceAll("=", "is");
					int last = divide[index].lastIndexOf("=");
					String one = divide[index].substring(0, last);
					String two = " is";
					String three = divide[index].substring(last + 1);
					sb.append(one).append(two).append(three);
					//	sql = divide.toString();
				}
				else
					sb.append(divide[index]);
				sb.append(" ?");
				index++;
			}
			if (divide.length == index + 1)
			{
				sb.append(divide[index]);
			}
			sql = sb.toString();

		}
		return sql;
	}

	/**
	 * Executes SQL-query. Returns data, conversion to which is provided by ${@link common.db.handlers.ResultSetHandler}.
	 *
	 * @param sql
	 * @param rsh
	 * @param params
	 * @return
	 */
	public <E> E query(String sql, ResultSetHandler<E> rsh, Object... params)
	{
		sql = correctSyntaxForNullParams(sql, params);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		E result = null;
		Connection connection = null;
		long timeStamp = 0;
		try
		{
			//log.debug("sql: " + sql + "\nparams: " + toString(params) + "\nResultSetHandler: " + rsh);
//			log.debug("sql: " + sql);
			connection = checkConnection();
			stmt = connection.prepareStatement(sql);
			this.fillStatement(stmt, params);
			timeStamp = timeStampBegin();
			rs = stmt.executeQuery();
			timeStamp = timeStampEnd(timeStamp);
//			log.debug("elapsedTime: " + timeStamp + " msec");
			result = rsh.handle(rs);
		}
		catch (SQLException e)
		{
//			log.error("SQLException: sql: " + sql + "\nparams: " + toString(params), e);
			rethrow(e, sql, params);
		}
		finally
		{
			try
			{
				close(rs);
			}
			finally
			{
				try
				{
					close(stmt);
				}
				finally
				{
					releaseConnection(connection);
				}
			}
		}

		return result;
	}

	/**
	 * Simplifier Sql-Update
	 *
	 * @param sqlParam
	 * @return
	 */
	public int update(SqlQueryParam sqlParam)
	{
		return update(sqlParam.getSql(), sqlParam.getParams());
	}

	/**
	 * Executes SQL-query.
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(String sql, Object... params)
	{
		PreparedStatement stmt = null;
		int rows = 0;
		Connection connection = null;
		long timeStamp = 0;
		try
		{
			//log.debug("sql: " + sql + "\nparams: " + toString(params));
//			log.debug("sql: " + sql);
			connection = checkConnection();

			stmt = connection.prepareStatement(sql);

			this.fillStatement(stmt, params);
			timeStamp = timeStampBegin();
			rows = stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();

			if (rs.next())
			{
				System.out.println(rs.getString(1));
			}
			timeStamp = timeStampEnd(timeStamp);

//			log.debug("elapsedTime: " + timeStamp + " msec, rows: " + rows);
		}
		catch (SQLException e)
		{
//			log.error("SQLException: sql: " + sql + "\nparams: " + toString(params), e);
			this.rethrow(e, sql, params);
		}
		finally
		{

			try
			{
				close(stmt);
			}
			finally
			{
				releaseConnection(connection);
			}
		}

		return rows;
	}

	private String[] prepareSqlScript(String sqlScript)
	{

		String groups[] = sqlScript.split("(delimiter|DELIMITER)\\s+\\S+");

		Matcher matcher = Pattern.compile("((delimiter|DELIMITER)\\s+)(\\S+)").matcher(sqlScript);

		String delimiters[] = new String[groups.length];
		delimiters[0] = ";";
		for (int i = 1; matcher.find() && i < delimiters.length; i++)
			delimiters[i] = matcher.group(3);


		ArrayList<String> list = new ArrayList<String>();

		for (short i = 0; i < groups.length; i++)
		{
			String del = groups[i];

			del = del.replaceAll("(/\\*.*\\*/)|(\\-\\-.*\r?\n)", "");
			del = del.replaceAll("\\s{2,}|\n|\r", " ");
			String stmts[] = del.split(delimiters[i]);
			for (String stmt : stmts)
			{
				if (stmt != null && !stmt.matches("\\s*"))
					list.add(stmt);
			}

		}
		return list.toArray(new String[0]);
	}

	public String executeScript(String sqlScript)
	{
		Statement stmt = null;
		Connection connection = null;

		connection = checkConnection();

		StringBuilder sb = new StringBuilder();
		try
		{
			stmt = connection.createStatement();

			String stmts[] = prepareSqlScript(sqlScript);

			for (String statement : stmts)
				stmt.addBatch(statement);

			int res[] = stmt.executeBatch();

			sb.append("\n\t========================== SQL Server responce: ==========================");
			sb.append("\n\tResponce \t\t Statement \n");
			for (int i = 0; i < res.length; i++)
			{
				sb.append("\n\t");
				sb.append(res[i]);
				sb.append("\t\t");
				sb.append(stmts[i]);
			}
			sb.append("\n\t============================ End of execution ============================");
		}
		catch (Exception ex)
		{
			throw new RuntimeException("Error Creating the SQL Database : " + ex.getMessage());
		}
		finally
		{

			try
			{
				close(stmt);
			}
			finally
			{
				releaseConnection(connection);
			}
		}

		return sb.toString();
	}

//	public void updateBlob(String sql, String blobStr, Object... params)
//	{
//		PreparedStatement stmt = null;
//		int rows = 0;
//		Connection connection = null;
//		long timeStamp = 0;
//		try
//		{
//			//log.debug("sql: " + sql + "\nparams: " + toString(params) + "\nblobStr: " + blobStr);
////			log.debug("sql: " + sql + "\nblobStr: " + blobStr);
//			connection = checkConnection();
//			stmt = connection.prepareStatement(sql);
//			this.fillStatement(stmt, params);
//			timeStamp = timeStampBegin();
//			ResultSet rs = stmt.executeQuery();
//			timeStamp = timeStampEnd(timeStamp);
////			log.debug("elapsedTime: " + timeStamp + " msec");
//			if (rs.next())
//			{
//				Blob blob = rs.getBlob(1);
//				if (blob != null)
//				{
//					if (blob.length() > blobStr.length())
//						blob.truncate(blobStr.length());
//					OutputStream os = blob.setBinaryStream(1);
////					log.trace("OutputStream: " + os + ", blobStr.length(): " + blobStr.length());
//					os.write(blobStr.getBytes(), 0, blobStr.length());
////					for (int i = 0; i < blob.length() - blobStr.length(); i++)
////						os.write();
//					os.close();
//				}
//			}
//		}
//		catch (SQLException e)
//		{
////			log.error("SQLException: sql: " + sql + "\nparams: " + toString(params), e);
//			this.rethrow(e, sql, params);
//		}
//		catch (IOException e)
//		{
////			log.error("IOException", e);
//			throw new RuntimeException(e);
//		}
//		finally
//		{
//			try
//			{
//				close(stmt);
//			}
//			finally
//			{
//				releaseConnection(connection);
//			}
//		}

	//	}


	public Object[] insertWithMultyReturn(String sql, List<String> forReturn, Object... params)
	{
		Object[] gen = null;
		PreparedStatement stmt = null;
		Connection connection = null;
		ResultSet rs = null;
		//long timeStamp = 0;
		try
		{
			connection = checkConnection();
			stmt = connection.prepareStatement(sql);

			this.fillStatement(stmt, params);
			//timeStamp = timeStampBegin();
			stmt.executeUpdate();
			rs = stmt.getGeneratedKeys();

			if (rs.first())
			{
				ResultSetMetaData meta = rs.getMetaData();
				gen = new Object[meta.getColumnCount()];
				forReturn.clear();
				for (int col = 0; col < gen.length; col++)
				{
					forReturn.add(meta.getColumnName(col + 1));
					gen[col] = rs.getObject(col + 1);
				}
			}

			//timeStamp = timeStampEnd(timeStamp);
		}
		catch (SQLException e)
		{
			this.rethrow(e, sql, params);
		}
		finally
		{
			try
			{
				close(rs);
			}
			finally
			{
				try
				{
					close(stmt);
				}
				finally
				{
					releaseConnection(connection);
				}
			}

		}
		return gen;
	}

	public Object[] insertWithMultyReturn(SqlQueryParam sqlParam)
	{
		return insertWithMultyReturn(sqlParam.getSql(), sqlParam.getForReturn(), sqlParam.getParams());
	}

	/**
	 * Id must be first
	 * Id can be what you want
	 *
	 * @param sqlParam
	 * @return
	 */
	public Object insertWithKeyReturn(SqlQueryParam sqlParam)
	{
		return insertWithKeyReturn(sqlParam.getSql(), sqlParam.getParams());
	}

	/**
	 * Id must be first
	 * Id can be what you want
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public Object insertWithKeyReturn(String sql, Object... params)
	{
		//return update(sql, new int[]{1}, OneRowHandler.INTEGER_HANDLER, params);PreparedStatement stmt = null;
		Object pk = null;
		PreparedStatement stmt = null;
		Connection connection = null;
		ResultSet rs = null;
		long timeStamp = 0;
		try
		{
			//log.debug("sql: " + sql + "\nparams: " + toString(params));
//			log.debug("sql: " + sql);
			connection = checkConnection();
			stmt = connection.prepareStatement(sql);

			this.fillStatement(stmt, params);
			timeStamp = timeStampBegin();
			stmt.executeUpdate();
			rs = stmt.getGeneratedKeys();

			if (rs != null && rs.next())
			{
				pk = rs.getObject(1);
				if (pk instanceof Long) pk = new Integer(pk.toString());
			}
			timeStamp = timeStampEnd(timeStamp);
//			log.debug("elapsedTime: " + timeStamp + " msec, rows: " + rows);
		}
		catch (SQLException e)
		{
//			log.error("SQLException: sql: " + sql + "\nparams: " + toString(params), e);
			this.rethrow(e, sql, params);
		}
		finally
		{
			try
			{
				close(rs);
			}
			finally
			{
				try
				{
					close(stmt);
				}
				finally
				{
					releaseConnection(connection);
				}
			}

		}

		return pk;
	}

	/**
	 * With Oracle DB should be used AdvancedQueryRunner.update(String sql, final Class[] returnTypes, Object... params)
	 * instead.
	 *
	 * @param sql
	 * @param dateIndex
	 * @param params
	 * @return
	 */
	public Object[] insertWithKeyAndDateReturn(String sql, final int dateIndex, Object... params)
	{
		OneRowHandler<Object[]> orh = new OneRowHandler<Object[]>()
		{
			@Override
			protected Object[] processRow(ResultSet rs) throws SQLException
			{
				Object[] objects = new Object[2];
				objects[0] = rs.getInt(1);
				objects[1] = rs.getTimestamp(2);
				return objects;
			}
		};
		return update(sql, new int[]{1, dateIndex}, orh, params);
	}

	/**
	 * With Oracle DB should be used AdvancedQueryRunner.update(String sql, final Class[] returnTypes, Object... params)
	 * instead.
	 *
	 * @param sql
	 * @param keyIndexes
	 * @param autogenRsh
	 * @param params
	 * @param <E>
	 * @return
	 */
	public <E> E update(String sql, int[] keyIndexes, OneRowHandler<E> autogenRsh, Object... params)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Connection connection = null;
		long timeStamp;
		E result = null;

		try
		{
			connection = checkConnection();
			stmt = connection.prepareStatement(sql, keyIndexes);
			this.fillStatement(stmt, params);
			//log.debug("sql: " + sql + "\nparams: " + toString(params));
//			log.debug("sql: " + sql);
			timeStamp = timeStampBegin();
			stmt.executeUpdate();
			timeStamp = timeStampEnd(timeStamp);
			rs = stmt.getGeneratedKeys();
			result = autogenRsh.handle(rs);
//			log.debug("elapsedTime: " + timeStamp + " msec");

		}
		catch (SQLException e)
		{
//			log.error("SQLException: sql: " + sql + "\nparams: " + toString(params), e);
			this.rethrow(e, sql, params);

		}
		finally
		{
			try
			{
				close(rs);
			}
			finally
			{
				try
				{
					close(stmt);
				}
				finally
				{
					releaseConnection(connection);
				}
			}
		}

		return result;
	}

	public String backupToFile(String path, String name)
	{
		Connection connection = checkConnection();
		Statement stmtb = null;
		Statement stmt = null;
		ResultSet rs = null;

		long time = 0;
		try
		{
			stmtb = connection.createStatement();
			stmt = connection.createStatement();
			stmt.execute("SHOW TABLES");
			rs = stmt.getResultSet();

			time = System.currentTimeMillis();

			while (rs.next())
			{
				String table = rs.getString(1);
				stmtb.execute(
						"SELECT * INTO OUTFILE '" + path + time + '-' + name + '_' + table + ".bak' FROM " + table);
			}
		}
		catch (SQLException e)
		{
			throw new RuntimeException("Error Backuping the SQL Database : " + e.getMessage());
		}
		finally
		{
			try
			{
				close(rs);
			}
			finally
			{
				try
				{
					close(stmtb);
					close(stmt);
				}
				finally
				{
					releaseConnection(connection);
				}
			}
		}
		return time + "-" + name;
	}

	public boolean restoreFromFile(String path, String name)
	{
		Connection connection = checkConnection();
		Statement stmt = null;
		ResultSet rs = null;

		boolean restored = false;
		try
		{

			stmt = connection.createStatement();
			stmt.execute("SHOW TABLES");
			rs = stmt.getResultSet();

			String table = null;
			while (rs.next())
			{
				table = rs.getString(1);
				stmt.addBatch(
						"LOAD DATA INFILE '" + path + name + '_' + table + ".bak' REPLACE INTO TABLE " + table);
			}
			stmt.executeBatch();

			restored = true;
		}
		catch (SQLException e)
		{
			throw new RuntimeException("Error Restoring the SQL Database : " + e.getMessage());
		}
		finally
		{
			try
			{
				close(rs);
			}
			finally
			{
				try
				{
					close(stmt);
				}
				finally
				{
					releaseConnection(connection);
				}
			}
		}
		return restored;
	}

	protected void rethrow(SQLException cause, String sql, Object[] params)
	{
//		try
//		{
//			rollback(true);
//		}
//		catch (SQLException e)
//		{
//			throw new SQLRuntimeException(e);
//		}
		throw new RuntimeException(cause.getMessage() + "\nsql: " + sql, cause);
	}

	protected void fillStatement(PreparedStatement stmt, Object... params)
			throws SQLException
	{
		if (params == null)
			return;

		for (int i = 0; i < params.length; i++)
		{
			System.out.println(params[i] + "  " + params.length);
			//			log.trace("params[" + i + "]: " + params[i]);
			if (params[i] instanceof java.util.Date)
				stmt.setTimestamp(i + 1,
						new Timestamp(((java.util.Date)params[i]).getTime()));
			else if (params[i] != null)
				stmt.setObject(i + 1, params[i]);
			else
			{
				stmt.setNull(i + 1, Types.VARCHAR);
			}
		}
	}

	protected Connection checkConnection()
	{
		Connection conn = null;
		while ((conn = DataBasePool.instance().getConnection(connectionParametar, 5000)) == null)
		{
			// TODO prijavi korisniku zastoj u dobijanju veze
			System.err.println("Isteklo cekanje za vezu sa bazom, cekam ponovo...");
		}
		return conn;

	}

	protected void close(ResultSet rs)
	{
		try
		{
//			log.trace("ResultSet: " + rs);
			rs.close();
		}
		catch (SQLException e)
		{
//			log.error("SQLException: ResultSet: " + rs, e);
			throw new RuntimeException(e);
		}
		catch (NullPointerException e)
		{
//			log.warn("NullPointerException for ResultSet", e);
		}
	}

	protected void close(Statement st)
	{
		try
		{
//			log.trace("Statement: " + st);
			st.close();
		}
		catch (SQLException e)
		{
//			log.error("SQLException: Statement: " + st, e);
			throw new RuntimeException(e);
		}
		catch (NullPointerException e)
		{
//			log.warn("NullPointerException for Statement", e);
		}
	}


	protected void releaseConnection(Connection connection)
	{
		DataBasePool.instance().relaseConnection(connection);
	}

	public void rollback(Connection connection, boolean close)
	{
		try
		{
//			log.trace("close: " + close);
			if (!autoCommit && dataSource == null)
			{
				connection.rollback();
				connection.setAutoCommit(true);
			}
		}
		catch (SQLException e)
		{
//			log.error("SQLException", e);
			throw new RuntimeException(e);
		}
		finally
		{
			try
			{
				if (close)
					connection.close();
			}
			catch (SQLException e)
			{
//				log.warn("SQLException", e);
			}
		}
	}

	public void rollback(Connection connection)
	{
		rollback(connection, true);
	}

	public void commit(Connection connection, boolean close)
	{
		try
		{
//			log.trace("close: " + close);
			if (!autoCommit && dataSource == null)
			{
				connection.commit();
				connection.setAutoCommit(true);
			}
		}
		catch (SQLException e)
		{
//			log.error("SQLException", e);
			throw new RuntimeException(e);
		}
		finally
		{
			try
			{
				if (close)
					connection.close();
			}
			catch (SQLException e)
			{
//				log.warn("SQLException", e);
			}
		}
	}

	public void commit(Connection connection)
	{
		commit(connection, true);
	}

	protected long timeStampBegin()
	{
		return System.currentTimeMillis();
	}

	protected long timeStampEnd(long timeStamp)
	{
		long timeDiff = 0;
		if (timeStamp > 0)
			timeDiff = System.currentTimeMillis() - timeStamp;
		else
			timeDiff = 0;
		return timeDiff;
	}

	protected String toString(Object[] params)
	{
		StringBuffer result = new StringBuffer("[");
		for (int i = 0; i < params.length; i++)
		{
			result.append(params[i]);
			if (i < params.length - 1)
				result.append(", ");
		}
		result.append("]");
		return result.toString();
	}
}
